﻿using MyCrawler.Forums;
using MyCrawler.Entity;
using MyCrawler.Utilyties;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyCrawler.Dals
{
    class ForumCatagoriesDal
    {
        internal string _cn;

        internal ForumCatagoriesDal()
        {
            _cn = UtilityCtr.GetConnectString();
        }

        /// <summary>
        /// Thêm mới 1 danh mục
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        internal int Insert(ForumCatagoriesEntity obj)
        {
            int i = 0;
            using (SqlConnection con = new SqlConnection(_cn))
            {
                try
                {
                    con.Open();

                    string q = "INSERT INTO [ForumCatagories]([IdSite],[IdParrent],[TopicName],[Url],[IsDeleted],[Ord]) VALUES ('" + obj.IdSite + "'";
                    q += ",'" + obj.IdParrent + "'";
                    q += ",N'" + obj.TopicName.Replace("'","''").Trim() + "'";
                    q += ",N'" + obj.Url.Replace("'", "''").Trim() + "'";
                    q += ",'" + obj.IsDeleted + "'";
                    q += ",'" + obj.Ord + "'";
                    q += ") declare @id int;select @id=ident_current('ForumCatagories');select @id";

                    SqlCommand cmd = new SqlCommand(q, con);
                    i = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch {  }
            }
            return i;
        }

        internal int Update(ForumCatagoriesEntity obj)
        {
            int i = 0;
            using (SqlConnection con = new SqlConnection(_cn))
            {
                try
                {
                    con.Open();

                    string q = "UPDATE [ForumCatagories] SET [IdParrent] = '" + obj.IdParrent + "'";
                    q += ",[TopicName] = N'" + obj.TopicName.Replace("'", "''").Trim() + "'";
                    q += ",[Url] = N'" + obj.Url.Replace("'", "''").Trim() + "'";
                    q += ",[Ord] = '" + obj.Ord + "'";
                    q += " WHERE [Id]='" + obj.Id + "'";

                    SqlCommand cmd = new SqlCommand(q, con);
                    cmd.ExecuteNonQuery();
                }
                catch { }
            }
            return i;
        }

        /// <summary>
        /// lấy danh sách các link không có con
        /// </summary>
        /// <returns></returns>
        internal List<ForumCatagoriesEntity> GetLisCatagoriesIsChild(int idSite)
        {
            List<ForumCatagoriesEntity> lis = new List<ForumCatagoriesEntity>();
            using (SqlConnection con = new SqlConnection(_cn))
            {
                try
                {
                    con.Open();

                    string q = "SELECT [Id],[TopicName],[Url] FROM [ForumCatagories] where [IsDeleted]='0' and [IdSite]='" + idSite + "'";
                    
                    SqlCommand cmd = new SqlCommand(q, con);

                    SqlDataReader read = cmd.ExecuteReader();

                    while (read.Read())
                    {
                        int id=Convert.ToInt32(read["Id"]);
                        int hasChild = GetTotalChild(id);
                        string name = Convert.ToString(read["TopicName"]);
                        if (hasChild == 0) // không có con
                        {
                            ForumCatagoriesEntity obj = new ForumCatagoriesEntity();

                            obj.Id = id;
                            obj.Url = Convert.ToString(read["Url"]);

                            lis.Add(obj);
                        }
                    }
                }
                catch { }
            }
            return lis;
        }

        /// <summary>
        /// lấy con đầu của 1 item
        /// </summary>
        /// <param name="idParrent"></param>
        /// <returns></returns>
        internal int GetTotalChild(int idParrent)
        {
            int idChild = 0;
            using (SqlConnection con = new SqlConnection(_cn))
            {
                try
                {
                    con.Open();

                    string q = "SELECT count([Id]) as Num FROM [ForumCatagories] where [IsDeleted]='0' and [IdParrent]='"+idParrent+"'";

                    SqlCommand cmd = new SqlCommand(q, con);

                    SqlDataReader read = cmd.ExecuteReader();

                    while (read.Read())
                    {
                        idChild = Convert.ToInt32(read["Num"]);
                    }
                }
                catch { }
            }
            return idChild;
        }
        /// <summary>
        /// lấy số lượng link thông qua Url
        /// </summary>
        /// <param name="idSite"></param>
        /// <param name="url"></param>
        /// <returns>= -1 không tồn tại, =0 là lỗi, >0 là có</returns>
        internal int GetIdByUrl(int idSite, string url)
        {
            int idChild = -1;
            using (SqlConnection con = new SqlConnection(_cn))
            {
                try
                {
                    con.Open();

                    string q = "SELECT top 1 [Id] FROM [ForumCatagories] where [IdSite]='" + idSite + "' and [Url]='" + url + "'";

                    SqlCommand cmd = new SqlCommand(q, con);

                    SqlDataReader read = cmd.ExecuteReader();

                    while (read.Read())
                    {
                        idChild = Convert.ToInt32(read["Id"]);
                    }
                }
                catch { idChild = 0; }
            }
            return idChild;
        }
    }
}
